<?php
set_time_limit(0);

include_once (dirname(__FILE__).'/config/database.php');
include_once (dirname(__FILE__).'/class/Log.inc');
include_once (dirname(__FILE__).'/class/Mysql/Exception.inc');
include_once (dirname(__FILE__).'/class/Mysql.inc');
error_log("daily report update ...");
define('LOCK_FILE', dirname(__FILE__).'/lock/cronScriptEvery15minute.lock');
define('LOG_LEVEL', 'DEBUG');

$LogFile = dirname(__FILE__).'/log/cronScriptEvery15minute'.date('Y.m.d').'.log';

$Log = new Zw_Log();
$Log->setCurrLogLevel(LOG_LEVEL);
$Log->setLogFile($LogFile);

$handle = fopen(LOCK_FILE, 'w+');
if(!flock($handle, LOCK_EX|LOCK_NB)){
	$Log->write('进程已经启动，请不要重复启动');
	exit();
}

$GmDb = new Zw_Mysql();
$GmDb->setLog($Log);
$GmDb->connect($gmDatabaseHost, $gmDatabaseUsername, $gmDatabasePassword, $gmDatabaseName);


// php script.php latest
//


$statDays = 3;
$statEndDateTime = date('Y-m-d 00:00:00', time() - $statDays * 86400);

try {
	
	//生成日报
	//--用户注册、创角
	$sql = "
		select
			`gameAreaId`,
			concat(substr(`registerDateTime`, 1, 10), ' 00:00:00') as datetime,
			count(*) as `registerAccountNum`,
			count(distinct registerIpAddress) as `registerIpAddressNum`,
			count(roleName) as `createRoleNum`
		from
			`t_data_game_account`
		where
			`registerDateTime` >= '{$statEndDateTime}'
		group by
			`gameAreaId`,
			`datetime`
	";
	
	if(!(($data = $GmDb->getArray($sql)) === false) ) {
		foreach($data as $v) {    
			$sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`registerAccountNum` = '{$v['registerAccountNum']}',
					`registerIpAddressNum` = '{$v['registerIpAddressNum']}',
					`createRoleNum` = '{$v['createRoleNum']}'
				on duplicate key update
					`registerAccountNum` = '{$v['registerAccountNum']}',
					`registerIpAddressNum` = '{$v['registerIpAddressNum']}',
					`createRoleNum` = '{$v['createRoleNum']}'
			";
			$GmDb->query($sql);
		}
	}
	
	//统计总注册数
	$sql = "select * from t_data_game_area where status>=4";
	$datetime = date('Y-m-d 00:00:00', time());
	if(!(($data = $GmDb->getArray($sql)) === false) ) {
	    foreach($data as $v) {
	        $gameAreaId = $v['gameAreaId'];
	        
	        $sql = "select count(*) as totalRegisterNum from t_data_game_account where gameAreaId='{$gameAreaId}'";
	        $totalData = $GmDb->getRow($sql);
	        $totalRegisterNum = $totalData['totalRegisterNum'];
	        //error_log("total reg sql = ".$sql);
	        
	        //总创角数
	        $sql = "select count(*) as totalCreateRoleNum from t_data_game_account where roleName is not NULL and gameAreaId='{$gameAreaId}'";
	        $totalData = $GmDb->getRow($sql);
	        $totalCreateRoleNum = $totalData['totalCreateRoleNum'];
	        
	        
	        $sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$datetime}',
                    `totalRegisterNum` = '{$totalRegisterNum}',
                    `totalCreateRoleNum` = '{$totalCreateRoleNum}'
				on duplicate key update
                    `totalRegisterNum` = '{$totalRegisterNum}',
                    `totalCreateRoleNum` = '{$totalCreateRoleNum}'
			";
	        //error_log("sql = ".$sql);
	        $GmDb->query($sql);
	    }
	}
	
	
	//--登录
	$sql = "
		select
			b.`gameAreaId`,
			concat(substr(a.`loginDateTime`, 1, 10), ' 00:00:00') as datetime,
			count(distinct a.`userId`) as loginAccountNum,
			count(distinct a.`loginIpAddress`) as loginIpAddressNum
		from
			`t_data_game_account_login` a
		left join
			`t_data_game_account` b
		on
			a.`userId` = b.`userId`
		where
			concat(substr(a.`loginDateTime`, 1, 10), ' 00:00:00') >= '{$statEndDateTime}'
		group by
			b.`gameAreaId`,
			`datetime`
	";
	//error_log("login sql = ".$sql);
	if( ! (($data = $GmDb->getArray($sql)) === false) ) {
		foreach($data as $v) {
			$sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`loginAccountNum` = '{$v['loginAccountNum']}',
					`loginIpAddressNum` = '{$v['loginIpAddressNum']}'
				on duplicate key update
					`loginAccountNum` = '{$v['loginAccountNum']}',
					`loginIpAddressNum` = '{$v['loginIpAddressNum']}'
			";
			//error_log("login2 sql = ".$sql);
			$GmDb->query($sql);
		}
	}
	
	//--在线
	$sql = "
		select
			`gameAreaId`,
			concat(substr(`statDateTime`, 1, 10)) as datetime,
			max(`onlineRoleNum`) as `maxOnlineRoleNum`,
			avg(`onlineRoleNum`) as `avgOnlineRoleNum`
		from
			`t_data_game_online`
		where
			`statDateTime` >= '{$statEndDateTime}'
		group by
			`gameAreaId`,
			`datetime`
	";
	
	if( ! (($data = $GmDb->getArray($sql)) === false) ) {
		foreach ($data as $v) {
			$sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`maxOnlineRoleNum` = '{$v['maxOnlineRoleNum']}',
					`avgOnlineRoleNum` = '{$v['avgOnlineRoleNum']}'
				on duplicate key update
					`maxOnlineRoleNum` = '{$v['maxOnlineRoleNum']}',
					`avgOnlineRoleNum` = '{$v['avgOnlineRoleNum']}'
			";
			$GmDb->query($sql);
		}
	}
	
	//累计充值、充值人数
	$sql = "
		select
			b.`gameAreaId`,
			sum(a.`money`) as totalPay,
			count(distinct a.`userId`) as totalPayAccountNum
		from
			`t_data_game_pay` a
		left join
			`t_data_game_account` b
		on
			a.`userId` = b.`userId`
		group by
			b.`gameAreaId`
	";
	$datetime = date('Y-m-d 00:00:00', time()-10);
	if( ! (($data = $GmDb->getArray($sql)) === false) ) {
	    foreach ($data as $v) {
	        $sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$datetime}',
                    `totalPay`  = '{$v['totalPay']}',
                    `totalPayAccountNum` = '{$v['totalPayAccountNum']}'
				on duplicate key update
                    `totalPay`  = '{$v['totalPay']}',
                    `totalPayAccountNum` = '{$v['totalPayAccountNum']}'
			";
	        $GmDb->query($sql);
	    }
	}
	
	//今日充值
	$sql = "
		select
			`gameAreaId`,
			concat(substr(`payDateTime`, 1, 10)) as datetime,
			sum(`money`) as payMoney,
			count(distinct a.`userId`) as payAccountNum
		from
			`t_data_game_pay` a
		left join
			`t_data_game_account` b
		on
			a.`userId` = b.`userId`
		where
			`payDateTime` >= '{$statEndDateTime}'
			and
			a.`orderId` not like 'SEND_VIP_%'
			and
			a.`orderId` not like 'TEST_%'
		group by
			`gameAreaId`,
			`datetime`
	";
	//error_log("pay sql = ".$sql);
	if( ! (($data = $GmDb->getArray($sql)) === false) ) {
		foreach ($data as $v) {
			$sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`payMoney` = '{$v['payMoney']}',
					`payAccountNum` = '{$v['payAccountNum']}'
				on duplicate key update
					`payMoney` = '{$v['payMoney']}',
					`payAccountNum` = '{$v['payAccountNum']}'
			";
			$GmDb->query($sql);
		}
	}
    
	//月卡、至尊卡、双卡
	$sql = "
		select
			`gameAreaId`,
			concat(substr(`operatorDateTime`, 1, 10)) as datetime,
			count(`monthCardType`) as monthCardNum
		from
			`t_data_month_card` a
		left join
			`t_data_game_account` b
		on
			a.`userId` = b.`userId`
		where
			`operatorDateTime` >= '{$statEndDateTime}'
			and
			`monthCardType` = 201
		group by
			`gameAreaId`,
			`datetime`
	";
	
	if(!(($data = $GmDb->getArray($sql)) === false) ) {
	    foreach ($data as $v) {
	        $sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`monthCardNum` = '{$v['monthCardNum']}'
				on duplicate key update
					`monthCardNum` = '{$v['monthCardNum']}'
			";
	        $GmDb->query($sql);
	    }
	}
	
	$sql = "
		select
			`gameAreaId`,
			concat(substr(`operatorDateTime`, 1, 10)) as datetime,
			count(`monthCardType`) as superCardNum
		from
			`t_data_month_card` a
		left join
			`t_data_game_account` b
		on
			a.`userId` = b.`userId`
		where
			`operatorDateTime` >= '{$statEndDateTime}'
			and
			`monthCardType` = 202
		group by
			`gameAreaId`,
			`datetime`
	";
	
	if(!(($data = $GmDb->getArray($sql)) === false) ) {
	    foreach ($data as $v) {
	        $sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`superCardNum` = '{$v['superCardNum']}'
				on duplicate key update
					`superCardNum` = '{$v['superCardNum']}'
			";
	        $GmDb->query($sql);
	    }
	}
	
	$sql = "
		select
			`gameAreaId`,
			concat(substr(`operatorDateTime`, 1, 10)) as datetime,
			count(`monthCardType`) as bothCardNum
		from
			`t_data_month_card` a
		left join
			`t_data_game_account` b
		on
			a.`userId` = b.`userId`
		where
			`operatorDateTime` >= '{$statEndDateTime}'
			and
			`monthCardType` = 203
		group by
			`gameAreaId`,
			`datetime`
	";
	
	if(!(($data = $GmDb->getArray($sql)) === false) ) {
	    foreach ($data as $v) {
	        $sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`bothCardNum` = '{$v['bothCardNum']}'
				on duplicate key update
					`bothCardNum` = '{$v['bothCardNum']}'
			";
	        $GmDb->query($sql);
	    }
	}
	
	//新用户充值，新用户充值人数
	$sql =     "select 
                    b.gameAreaId as gameAreaId,
                    left(a.payDateTime,10)as datetime, 
                    sum(a.money) as payMoneyNew, 
                    count(DISTINCT a.userId)as payAccountNumNew 
                from
                    t_data_game_pay a
            	RIGHT JOIN 
                    t_data_game_account b 
                ON 
                    left(b.registerDateTime,10) = left(a.payDateTime,10) 
                and 
                    a.userId = b.userId 
                where 
                    a.payDateTime>'{$statEndDateTime}'
                GROUP BY 
                    `gameAreaId`,
					`datetime`;";
    //error_log("new pay sql = ".$sql);
	if(!(($data = $GmDb->getArray($sql)) === false) ) {
	    foreach ($data as $v) {
	        $sql = "
				insert into
					`t_data_daily_report`
				set
					`gameAreaId` = '{$v['gameAreaId']}',
					`datetime` = '{$v['datetime']}',
					`payMoneyNew` = '{$v['payMoneyNew']}',
					`payAccountNumNew` = '{$v['payAccountNumNew']}'
				on duplicate key update
					`payMoneyNew` = '{$v['payMoneyNew']}',
					`payAccountNumNew` = '{$v['payAccountNumNew']}'
			";
	        $GmDb->query($sql);
	    }
	}
	
    // 新加入代码从这开始
    // No.301-340: 已计算了createRoleNum及payMoney、payAccountNum值
    $sql = "SELECT * FROM `t_data_daily_report` WHERE `datetime` > '$statEndDateTime'";
    //error_log("update all sql = ".$sql);
    if(!(($data = $GmDb->getArray($sql)) === false) ) {
        foreach ($data as $v) {
            $payRateNum = 0;
            $arpu = 0;
            $arppu = 0;
            
            $payRateNumNew = 0;
            $arpuNew = 0;
            $arppuNew = 0;
            
            $payMoneyOld = 0;
            $payAccountNumOld = 0;
            $payRateNumOld = 0;
            $arpuOld = 0;
            $arppuOld = 0;
            
            if ($v['totalRegisterNum'] > 0) {
                $payRateNum = $v['totalPayAccountNum']/$v['totalRegisterNum'];
                $arpu = $v['totalPay']/$v['totalRegisterNum'];
            }
            if ($v['totalPayAccountNum'] > 0){
                $arppu = $v['totalPay'] / $v['totalPayAccountNum'];
            }
            
            if($v['registerAccountNum'] > 0)
            {
                $payRateNumNew = $v['payAccountNumNew']/$v['registerAccountNum'];
                $arpuNew = $v['payMoneyNew']/$v['registerAccountNum'];
            }
            if($v['payAccountNumNew'] > 0)
            {
                $arppuNew = $v['payMoneyNew']/$v['payAccountNumNew'];
            }
            
            $temp_registerAccountOld = $v['totalRegisterNum'] - $v['registerAccountNum'];
            $payMoneyOld = $v['totalPay'] - $v['payMoneyNew'];
            if($payMoneyOld < 0) $payMoneyOld = 0;
            $payAccountNumOld = $v['totalPayAccountNum'] - $v['payAccountNumNew'];
            if($temp_registerAccountOld > 0)
            {
                $payRateNumOld = $payAccountNumOld/$temp_registerAccountOld;
                $arpuOld = $payMoneyOld/$temp_registerAccountOld;                
            }
            if($payAccountNumOld < 0) $payAccountNumOld = 0;
            if($payAccountNumOld > 0)$arppuOld = $payMoneyOld/$payAccountNumOld;
            
            $sql = "UPDATE `t_data_daily_report` SET 
                        `payRateNum` = {$payRateNum}, 
                        `arpu` = {$arpu},
                        `arppu` = {$arppu},
                        `payRateNumNew` = {$payRateNumNew}, 
                        `arpuNew` = {$arpuNew},
                        `arppuNew` = {$arppuNew},

                        `payMoneyOld` = {$payMoneyOld},
                        `payAccountNumOld` = {$payAccountNumOld},
                        `payRateNumOld` = {$payRateNumOld},
                        `arpuOld` = {$arpuOld},
                        `arppuOld` = {$arppuOld}
                    WHERE `id` = '{$v['id']}'";
            //error_log("update sql = ".$sql);
            $GmDb->query($sql);
        }
    }
    // 新加入从这里结束


}

catch(Zw_Mysql_Exception $e) {
	$Log->write('数据库错误', 'ERROR');
}
catch(Exception $e) {
	$Log->write('系统错误', 'ERROR');
}